# -*- coding: utf-8 -*-
import random
from flask import Flask, render_template, request, url_for, redirect, session, jsonify
import os
from datetime import timedelta
from dbSqlite3 import *
import time
import sqlite3
from flask import Flask, render_template, request, url_for, redirect
import sys
from flask import Flask, flash, request, redirect, url_for
from werkzeug.utils import secure_filename
from flask import send_from_directory
import numpy as np
import tkinter as tk
from tkinter import filedialog
from flask import Flask, render_template, request, redirect, url_for, make_response, jsonify,session
from werkzeug.utils import secure_filename
import os
import time
from datetime import timedelta
import xlrd
import datetime
app = Flask(__name__)
app.config['SECRET_KEY'] = os.urandom(24)
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=7)
# username = ""
# usertype = ""


app = Flask(__name__)
app.secret_key = 'abcdefgh!@#$%'


@app.route('/', methods=['POST', 'GET'])
def login():
    if request.method == 'POST':
        ls_username = request.form['username']
        for i in ls_username:
            if i > '9' or i < '0':
                return render_template('login.html', wout='用户名只包含数字')
        if len(ls_username) != 10:
            return render_template('login.html', wout='用户名长度有误')
        ls_password = request.form['password']
        ls_type = request.form['user_type']
        result, _ = GetSql2("select * from {0} where id='{1}'".format(ls_type, ls_username))
        if len(result) == 0 or result[0][1] != ls_password:
            return render_template('login.html', wout='用户名或密码错误')
        session['username'] = ls_username
        session['user_type'] = ls_type
        if ls_type == 'stu':
            # 重定向到对应的html文件
            return redirect(url_for('xch_ser_courses'))
        else:
            if ls_type == 'tea':
                return redirect(url_for('check_class_details'))
            else:
                return redirect(url_for('manager'))
    else:
        if session.get('username') is not None:
            if session.get('user_type') == 'adm':
                return redirect(url_for('manager'))
            if session.get('user_type') == 'tea':
                return redirect(url_for('check_class_details'))
            if session.get('user_type') == 'stu':
                return redirect(url_for('xch_ser_courses'))
        else:
            return render_template('login.html', wout='')


@app.route('/lcy_up_pas', methods=['POST'], strict_slashes=False)
def lcy_up_pas():
    ls_name = request.form['username']
    ls_pas = request.form['password1']
    data = dict(
        id=ls_name,
        pas=ls_pas,
    )
    UpdateData(data, "adm")
    return jsonify({"success": 200, "flag": True})


@app.route('/cg_lcy_up_pas', methods=['POST'], strict_slashes=False)
def cg_lcy_up_pas():
    ls_name = request.form['username']
    ls_pas = request.form['password1']
    print(ls_name)
    print(ls_pas)
    data = dict(
        id=ls_name,
        pas=ls_pas,
    )
    print(data)
    UpdateData(data, "tea")
    return jsonify({"success": 200, "flag": True})


@app.route('/lcy_log_out', methods=['POST'], strict_slashes=False)
def lcy_log_out():
    session.pop('username')
    session.pop('user_type')
    return jsonify({"success": 200, "flag": True})

@app.route('/lcy_up_stu', methods=['POST'], strict_slashes=False)
def lcy_up_stu():
    xh = request.form['xh']
    xm = request.form['xm']
    xb = request.form['xb']
    sr = request.form['sr']
    bj = request.form['bj']
    mm = request.form['mm']
    data = dict(
        id=xh,
        pas=mm,
        name=xm,
        c_name=bj,
        sex=xb,
        date=sr,
    )
    UpdateData(data, "stu")
    ls_text = '''<td>
                    <button type="button" class="btn btn-link" onclick="to_stu({0})">
                        <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                    </button>
                </td>
                <td id="stu_xh{0}">
                    {0}
                </td>
                <td id="stu_xm{0}">
                    {2}
                </td>
                <td id="stu_bj{0}">
                    {3}
                </td>
                <td id="stu_xb{0}">
                    {4}
                </td>
                <td id="stu_cs{0}">
                    {5}
                </td>
                <td id="stu_mm{0}">
                    {1}
                </td>
                <td>
                    <button type="button" class="btn btn-link" onclick="del_stu({0})">
                        <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                    </button>
                </td>'''.format(xh, mm, xm, bj, xb, sr)
    # print(ls_text)
    return jsonify({"success": 200, "flag": True, "text": ls_text})


@app.route('/lcy_cr_stu', methods=['POST'], strict_slashes=False)
def lcy_cr_stu():
    # xh = request.form['xh']
    xm = request.form['xm']
    xb = request.form['xb']
    sr = request.form['sr']
    bj = request.form['bj']
    mm = request.form['mm']
    # xh = "0123456789"
    xh = str(datetime.datetime.now().year) + str(datetime.datetime.now().second).zfill(2) + str(random.randint(0, 9999)).zfill(4)
    data = dict(
        id=xh,
        pas=mm,
        name=xm,
        c_name=bj,
        sex=xb,
        date=sr,
    )
    InsertData(data, "stu")
    ls_text = '''<tr id="stu{0}">
                    <td>
                        <button type="button" class="btn btn-link" onclick="to_stu({0})">
                            <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                        </button>
                    </td>
                    <td id="stu_xh{0}">
                        {0}
                    </td>
                    <td id="stu_xm{0}">
                        {2}
                    </td>
                    <td id="stu_bj{0}">
                        {3}
                    </td>
                    <td id="stu_xb{0}">
                        {4}
                    </td>
                    <td id="stu_cs{0}">
                        {5}
                    </td>
                    <td id="stu_mm{0}">
                        {1}
                    </td>
                    <td>
                        <button type="button" class="btn btn-link" onclick="del_stu({0})">
                            <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                        </button>
                    </td>
                <tr>'''.format(xh, mm, xm, bj, xb, sr)
    # print(ls_text)
    return jsonify({"success": 200, "flag": True, "text": ls_text, "xh": xh})


@app.route('/lcy_del_stu', methods=['POST'], strict_slashes=False)
def lcy_del_stu():
    xh = request.form['id']
    DelDataById('id', xh, 'stu')
    return jsonify({"success": 200, "flag": True})


@app.route('/lcy_up_open', methods=['POST'], strict_slashes=False)
def lcy_up_open():
    bh = request.form['bh']
    kh = request.form['kh']
    js = request.form['js']
    cl = request.form['cl']
    xs = request.form['xs']
    xf = request.form['xf']
    rl = request.form['rl']
    da = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    data = dict(
        num=bh,
        co_id=kh,
        t_id=js,
        c_id=cl,
        hours=xs,
        credits=xf,
        people=rl,
        date=da,
        left_people=rl,
    )
    UpdateData(data, "open")
    ls_text = '''<td>
                    <button type="button" class="btn btn-link" onclick="to_open({0})">
                        <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                    </button>
                </td>
                <td id='op_bh{0}'>
                    {0}
                </td>
                <td id='op_kh{0}'>
                    {1}
                </td>
                <td id='op_js{0}'>
                    {2}
                </td>
                <td id='op_cl{0}'>
                    {3}
                </td>
                <td id='op_xs{0}'>
                    {4}
                </td>
                <td id='op_xf{0}'>
                    {5}
                </td>
                <td id='op_rl{0}'>
                    {6}
                </td>
                <td id='op_date{0}'>
                    {7}
                </td>
                <td>
                    <button type="button" class="btn btn-link" onclick="del_open({0})">
                         <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                    </button>
                </td>'''.format(bh, kh, js, cl, xs, xf, rl, da)
    return jsonify({"success": 200, "flag": True, "text": ls_text})


@app.route('/lcy_cr_open', methods=['POST'], strict_slashes=False)
def lcy_cr_open():
    kh = request.form['kh']
    js = request.form['js']
    cl = request.form['cl']
    xs = request.form['xs']
    xf = request.form['xf']
    rl = request.form['rl']
    da = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    data = dict(
        co_id=kh,
        t_id=js,
        c_id=cl,
        hours=xs,
        credits=xf,
        people=rl,
        date=da,
        left_people=rl,
    )
    InsertData(data, "open")
    rs, _ = GetSql2("select * from open where co_id='{0}' and t_id='{1}' and c_id='{2}' and hours='{3}' and credits='{4}' and people='{5}' and date='{6}'".format(kh, js, cl, xs, xf, rl, da))
    bh = rs[0][0]
    ls_text = '''<tr id="open{0}">
                    <td>
                        <button type="button" class="btn btn-link" onclick="to_open({0})">
                            <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                        </button>
                    </td>
                    <td id='op_bh{0}'>
                        {0}
                    </td>
                    <td id='op_kh{0}'>
                        {1}
                    </td>
                    <td id='op_js{0}'>
                        {2}
                    </td>
                    <td id='op_cl{0}'>
                        {3}
                    </td>
                    <td id='op_xs{0}'>
                        {4}
                    </td>
                    <td id='op_xf{0}'>
                        {5}
                    </td>
                    <td id='op_rl{0}'>
                        {6}
                    </td>
                    <td id='op_date{0}'>
                        {7}
                    </td>
                    <td>
                        <button type="button" class="btn btn-link" onclick="del_open({0})">
                             <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                        </button>
                    </td>
                </tr>'''.format(bh, kh, js, cl, xs, xf, rl, da)
    return jsonify({"success": 200, "flag": True, "text": ls_text, "bh": bh})


@app.route('/lcy_del_open', methods=['POST'], strict_slashes=False)
def lcy_del_open():
    bh = request.form['id']
    DelDataById('num', bh, 'open')
    return jsonify({"success": 200, "flag": True})


@app.route('/lcy_ser_open', methods=['POST'], strict_slashes=False)
def lcy_ser_open():
    data = []
    kh = request.form['kh']
    js = request.form['js']
    bj = request.form['bj']
    re, _ = GetSql2("select * from open where co_id like '%{0}%' and t_id like '%{1}%' and c_id like '%{2}%'".format(kh, js, bj))
    print(re)
    for i in re:
        data.append('''<tr id="open{0}">
                    <td>
                        <button type="button" class="btn btn-link" onclick="to_open({0})">
                            <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                        </button>
                    </td>
                    <td id='op_bh{0}'>
                        {0}
                    </td>
                    <td id='op_kh{0}'>
                        {1}
                    </td>
                    <td id='op_js{0}'>
                        {2}
                    </td>
                    <td id='op_cl{0}'>
                        {3}
                    </td>
                    <td id='op_xs{0}'>
                        {4}
                    </td>
                    <td id='op_xf{0}'>
                        {5}
                    </td>
                    <td id='op_rl{0}'>
                        {6}
                    </td>
                    <td id='op_date{0}'>
                        {7}
                    </td>
                    <td>
                        <button type="button" class="btn btn-link" onclick="del_open({0})">
                             <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                        </button>
                    </td>
                </tr>'''.format(i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7]))
    print(data)
    return jsonify({"success": 200, "flag": True, "data": data})


@app.route('/lcy_ser_stu', methods=['POST'], strict_slashes=False)
def lcy_ser_stu():
    data = []
    xh = request.form['xh']
    xm = request.form['xm']
    bj = request.form['bj']
    re, _ = GetSql2(
        "select * from stu where id like '%{0}%' and name like '%{1}%' and c_name like '%{2}%'".format(xh, xm, bj))
    for i in re:
        data.append('''<tr id="stu{0}">
                            <td>
                                <button type="button" class="btn btn-link" onclick="to_stu({0})">
                                    <span class="glyphicon glyphicon-arrow-left" style="color: Green"></span>
                                </button>
                            </td>
                            <td id="stu_xh{0}">
                                {0}
                            </td>
                            <td id="stu_xm{0}">
                                {2}
                            </td>
                            <td id="stu_bj{0}">
                                {3}
                            </td>
                            <td id="stu_xb{0}">
                                {4}
                            </td>
                            <td id="stu_cs{0}">
                                {5}
                            </td>
                            <td id="stu_mm{0}">
                                {1}
                            </td>
                            <td>
                                <button type="button" class="btn btn-link" onclick="del_stu({0})">
                                    <span class="glyphicon glyphicon-remove" style="color: Red"></span>
                                </button>
                            </td>
                        <tr>'''.format(i[0], i[1], i[2], i[3], i[4], i[5]))
    return jsonify({"success": 200, "flag": True, "data": data})


@app.route('/manager', methods=['GET', 'POST'])
def manager():
    if session.get('username') is not None:
        adm = session.get('username')
        majors = GetSql2('select * from major')
        teas = GetSql2('select * from tea')
        stus = GetSql2('select * from stu')
        clas = GetSql2('select * from class')
        cous = GetSql2('select * from course')
        opens = GetSql2('select * from open')
        days = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']
        tims = ['1-2', '3-4', '5-6', '7-8', '9-11']
        return render_template('manager.html', adm=adm, majors=majors, stus=stus, teas=teas, clas=clas, cous=cous, opens=opens, days=days, tims=tims)
    else:
        return redirect(url_for('login'))



def xz():
    root = tk.Tk()
    root.withdraw()

    # Folderpath = filedialog.askdirectory()  # 获得选择好的文件夹
    Filepath = filedialog.askopenfilename()  # 获得选择好的文件

    # print('Folderpath:', Folderpath)
    print('Filepath:', Filepath)
    return Filepath

def get_data(filename,method='r'):
    '''
    改变数据结构 -- 方便前端显示
    :param filename:  文件名
    :param method:  按照 列或者 行 返回数据
    '''
    data = xlrd.open_workbook(filename)
    table= data.sheets()[0]
    nrows = table.nrows  # 行数
    ncols = table.ncols  # 列数
    if method == 'r':
        row_list = [ table.row_values(i) for i in range(0,nrows)]   # 所有行的数据
        return row_list
    elif method == 'c':
        col_list = [ table.col_values(i) for i in range(0,ncols)]    # 所有列的数据
        return col_list


@app.route('/update',methods=['GET','POST'])
def update():
    if session.get('username') is not None:
        username=session.get('username')

    return render_template('cg_Login.html')

@app.route('/check_class_details',methods=['GET','POST'])
def check_class_details():
    if session.get('username') is not None:
        username=session.get('username')
        myself, _ = GetSql2("select * from tea where id='%s'" % username)
        courses, _ = GetSql2("select * from open_view where 教师编号='%s'"% username)
        print(courses)
        return render_template('check_class_details.html',  courses=courses,myself=myself)
    else:
        return redirect(url_for('login'))
@app.route('/check_class_details1',methods=['GET','POST'])
def check_class_details1():
    if session.get('username') is not None:
        username=session.get('username')
        myself, _ = GetSql2("select * from tea where id='%s'"% username)
        courses, _ = GetSql2("select * from open_view where 教师编号='%s'"% username)
        print(courses)
        cno = request.args['his_class']
        print("选择的课程是：", cno)
        students, fields = GetSql2("select * from choice_view where 课程号 ='%s'" % cno)
        return render_template('check_class_details.html', myself=myself,fields=fields,datas=students,courses=courses)
    else:
        return redirect(url_for('login'))
#excel表格录入
@app.route('/check_students_grades',methods=['POST','GET'])
def check_students_grades():
    if session.get('username') is not None:
        username = session.get('username')
        myself, _ = GetSql2("select * from tea where id='%s'"% username)
        courses, _ = GetSql2("select * from open_view where 教师编号='%s'"% username)
        print(courses)
        filename=""
        if request.method == 'POST':
            f = request.files['file']
            print(request.files)
            user_input = request.form.get("name")
            basepath = os.path.dirname(__file__)  # 当前文件所在路径
            upload_path = os.path.join(basepath, 'static/images',
                                       secure_filename(f.filename))  # 注意：没有的文件夹一定要先创建，不然会提示没有该路径
            # upload_path = os.path.join(basepath, 'static/images','test.jpg')  #注意：没有的文件夹一定要先创建，不然会提示没有该路径
            f.save(upload_path)
            filename = upload_path
        if(filename!=""):
            print(filename)
            row_list=get_data(filename)
            # tablename = "course"
            # result, fields = GetSql2("select * from %s" % tablename)
            return render_template('check_students_grades.html', myself=myself,datas=row_list,courses=courses)
        else:
            return render_template('check_students_grades.html',myself=myself,courses=courses)
    else:
        return redirect(url_for('login'))
@app.route('/confirm_grades',methods=['POST','GET'])
def confirm_grades():
    if session.get('username') is not None:
        username = session.get('username')
        cno=session.get('cno')
        myself, _ = GetSql2("select * from tea where id='%s'"% username)
        if request.method == 'POST':
            courses, _ = GetSql2("select * from open_view where 教师编号='%s'"% username)
            m = request.form
            print("课程号",cno)
            n=dict(m)
            database = "./db/stu.db"
            conn = sqlite3.connect(database)
            cur = conn.cursor()
            for (i,j) in n.items():
                # 组建新的更新语句
                sql = "update choice set grade='%s' where stu_id = '%s' and co_id='%s'" % (
                j,i,cno)
                cur.execute(sql)
                conn.commit()
            cur.close()
            print("学生的学号是", n.keys())
            print("学生的分数是",n.values())
        #return redirect(url_for("check_students_grades.html"))
        session.pop('cno')
        return render_template('check_students_grades.html',myself=myself,courses=courses)
    else:
        return redirect(url_for('login'))
#手动录入
@app.route('/see_students',methods=['GET','POST'])
def see_students():
    if session.get('username') is not None:
        username = session.get('username')
        myself, _ = GetSql2("select * from tea where id='%s'" % username)
        cno = request.args['his_class']
        # 老师教的所有课
        courses, _ = GetSql2("select * from open_view where 教师编号='%s'"% username)
        print(courses)
        # 暂存选择的课程号，传递到confirm_grades()
        session['cno'] = cno
        print("选择的课程是：",cno)

        students, fields = GetSql2("select 学号,学生名,班级名,课程号,课程名 from choice_view where 课程号 ='%s'" % cno)

        return render_template('check_students_grades.html',cno=cno, myself=myself,datas=students,fields=fields,courses=courses)
    else:
        return redirect(url_for('login'))


# 初始化界面，查找所有的已开课
@app.route('/xch_ser_courses', methods=['GET', 'POST'])
def xch_ser_courses():
    # 老师教的所有课
    if session.get('username') is not None:
        username = session.get('username')
        # 查学生班级名
        myclass, _ = GetSql2("select c_name from stu where id='%s'" % username)
        # 查班级所开课程
        courses, _ = GetSql2("select 课程编号,课程名,剩余量,课程类型,教师名 from open_view where 班级名='%s'" % myclass[0][0])
        # 查看学生已选课
        choices, _ = GetSql2('''
            select choice_view.课程号,choice_view.课程名,open_view.课程类型,open_view.教师名
            from choice_view,open_view
            where choice_view.课程号=open_view.课程编号 and choice_view.班级名=open_view.班级名
                    and choice_view.学号="%s"''' % username)
        grades, _=GetSql2('''
            select choice_view.课程号,choice_view.课程名,open_view.课程类型,open_view.教师名,choice_view.成绩
            from choice_view,open_view
            where choice_view.课程号=open_view.课程编号 and choice_view.班级名=open_view.班级名
                    and choice_view.学号="%s"''' % username)

        stu_details, _ = GetSql2("select main.stu.id,main.stu.name,main.stu.sex,main.stu.date,main.stu.c_name from stu where id='%s'" % username)
        stu_num = stu_details[0][0]
        stu_name = stu_details[0][1]
        stu_sex = stu_details[0][2]
        stu_birth = stu_details[0][3]
        stu_class = stu_details[0][4]
        return render_template("student_details.html", courses=courses, choices=choices, grades=grades, stu_num=stu_num, stu_name=stu_name, stu_sex=stu_sex, stu_birth=stu_birth, stu_class=stu_class)
    else:
        return redirect(url_for('login'))
# 判断数字函数
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False
# 删除已选课程
@app.route('/xch_del_co', methods=['POST'], strict_slashes=False)
def xch_del_co():
    if session.get('username') is not None:
        stu_id =session.get('username')
        co_id = request.form['cho_num']
        # 查询是否已出成绩，已出成绩不再支持退课；后期可加上时间戳，到时间，数据库不允许选课
        grades, _ = GetSql2('''
                    select choice_view.课程号,choice_view.课程名,open_view.课程类型,open_view.教师名,choice_view.成绩
                    from choice_view,open_view
                    where choice_view.课程号=open_view.课程编号 and choice_view.班级名=open_view.班级名
                            and choice_view.学号="%s"''' % stu_id)
        # 查询学生班级id
        ca, _ = GetSql2('''
            select main.class.id from class
            where name=(select main.stu.c_name from stu where main.stu.id='%s')
        ''' % stu_id)
        class_id = ca[0][0]
       # print(grades)
        flag = True
        for i in grades:
            if i[0] == co_id and is_number(str(i[4])):
                flag = False
                break
        if flag == True:
            DelDataById2('stu_id', stu_id, 'co_id', co_id, 'choice')
            # 剩余人数增加
            sql = '''
            update open set left_people=left_people+1
            where c_id='%s' and co_id='%s';''' % (class_id, co_id)
            conn = OpenDb()
            cur = conn.cursor()
            cur.execute(sql)
            conn.commit()
            CloseDb(conn)
        else:
            pass
        return redirect(url_for('xch_ser_courses'))
    else:
        return redirect(url_for('login'))


# 选中课程
@app.route('/xch_co_co',methods=['POST'], strict_slashes=False)
def xch_co_co():
    if session.get('username') is not None:
        stu_id =session.get('username')
        co_id = request.form['co_num']
        print(co_id)
        data = dict(
            stu_id=stu_id,
            co_id=co_id,
        )
        # 查看学生已选课
        choices, _ = GetSql2('''
                    select choice_view.课程号,choice_view.课程名,open_view.课程类型,open_view.教师名
                    from choice_view,open_view
                    where choice_view.课程号=open_view.课程编号 and choice_view.班级名=open_view.班级名
                            and choice_view.学号="%s"''' % stu_id)
        # 查询学生班级id
        ca, _ = GetSql2('''
                    select main.class.id from class
                    where name=(select main.stu.c_name from stu where main.stu.id='%s')
                ''' % stu_id)
        class_id = ca[0][0]

        # 获得课程剩余量
        left, _ = GetSql2('''select left_people from open
                    where c_id='%s' and co_id='%s';''' % (class_id, co_id))
        left_num = left[0][0]
        # 遍历已选课程查看是否已选该课程,已选课程不用重新选课
        flag = True
        for i in choices:
            if i[0] == co_id:
                flag = False
                break
        # 如果剩余人数为0，不支持选课
        if left_num <= 0:
            flag = False

        if flag == True:
            InsertData(data, "choice")
            # 剩余人数减少
            sql = '''
                        update open set left_people=left_people-1
                        where c_id='%s' and co_id='%s';''' % (class_id, co_id)
            print(sql)
            conn = OpenDb()
            cur = conn.cursor()
            cur.execute(sql)
            conn.commit()
            CloseDb(conn)
        else:
            pass
        return redirect(url_for('xch_ser_courses'))
    else:
        return redirect(url_for('login'))



if __name__ == '__main__':
    # app.run("0.0.0.0",debug=True)
    app.run(debug=True)
